查看表和数据库的信息 |
您所在的位置：网站首页 › guass 数据库 索引 › 查看表和数据库的信息 |
查看表和数据库的信息
|
查询表信息
使用系统表pg_tables查询数据库所有表的信息。
1
SELECT * FROM pg_tables;
使用gsql的\d+命令查询表结构。
示例：先创建表customer_t1并插入数据。
1
2
3
4
5
6
7
8
9
CREATE TABLE customer_t1
(
c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
)
with (orientation = column,compression=middle)
distribute by hash (c_last_name);
1
2
3
4
INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES
(6885, 'map', 'Peter'),
(4321, 'river', 'Lily'),
(9527, 'world', 'James');
查询表结构。（若建表时不指定schema，则表的默认schema为public） 1 2 3 4 5 6 7 8 9 10 11 12 \d+ customer_t1; Table "public.customer_t1" Column | Type | Modifiers | Storage | Stats target | Description ---------------+--------------+-----------+----------+--------------+------------- c_customer_sk | integer | | plain | | c_customer_id | character(5) | | extended | | c_first_name | character(6) | | extended | | c_last_name | character(8) | | extended | | Has OIDs: no Distribute By: HASH(c_last_name) Location Nodes: ALL DATANODES Options: orientation=column, compression=middle, colversion=2.0, enable_delta=false
此处的Options在不同版本会有差异，对实际业务没有影响，仅作参考，实际以用户当前版本查询为准。 使用函数pg_get_tabledef查询表定义。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT * FROM PG_GET_TABLEDEF('customer_t1'); pg_get_tabledef ----------------------------------------------------------------------------------- SET search_path = tpchobs; + CREATE TABLE customer_t1 ( + c_customer_sk integer, + c_customer_id character(5), + c_first_name character(6), + c_last_name character(8) + ) + WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+ DISTRIBUTE BY HASH(c_last_name) + TO GROUP group_version1; (1 row) 执行如下命令查询表customer_t1的所有数据。 1 2 3 4 5 6 7 SELECT * FROM customer_t1; c_customer_sk | c_customer_id | c_first_name | c_last_name ---------------+---------------+--------------+------------- 6885 | map | Peter | 4321 | river | Lily | 9527 | world | James | (3 rows) 使用SELECT查询表customer_t1中某一字段的所有数据。 1 2 3 4 5 6 7 SELECT c_customer_sk FROM customer_t1; c_customer_sk --------------- 6885 4321 9527 (3 rows) 查询表是否做过表分析，执行如下命令会返回每个表最近一次做analyze的时间，没有返回时间的则表示没有做过analyze。 1 SELECT pg_stat_get_last_analyze_time(oid),relname FROM pg_class where relkind='r';查询public下的表做表分析的时间： 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT pg_stat_get_last_analyze_time(c.oid),c.relname FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind='r' AND n.nspname='public'; pg_stat_get_last_analyze_time | relname -------------------------------+---------------------- 2022-05-17 07:48:26.923782+00 | warehouse_t19 2022-05-17 07:48:26.964512+00 | emp 2022-05-17 07:48:27.016709+00 | test_trigger_src_tbl 2022-05-17 07:48:27.045385+00 | customer 2022-05-17 07:48:27.062486+00 | warehouse_t1 2022-05-17 07:48:27.114884+00 | customer_t1 2022-05-17 07:48:27.172256+00 | product_info_input 2022-05-17 07:48:27.197014+00 | tt1 2022-05-17 07:48:27.212906+00 | timezone_test (9 rows) 快速查到一张表的列信息，information_schema下的视图在数据库中对象较多时返回结果很慢，可以通过以下sql快速查询到一张或几张表的列信息： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 SELECT /*+ set (enable_hashjoin off) */T.table_schema AS tableschema, T.TABLE_NAME AS tablename, T.dtd_identifier AS srcAttrId, COLUMN_NAME AS fieldName, 'N' AS isPrimaryKey, nvl ( nvl ( T.character_maximum_length, T.numeric_precision ), 0 ) AS fieldLength, T.udt_name AS fieldType from ( SELECT /*+ indexscan(co) indexscan(nco) indexscan(a) indexscan(t) leading((nc c a)) leading((co nco)) indexscan(bt) indexscan(nt) */ nc.nspname AS table_schema, c.relname AS table_name, a.attname AS column_name, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, a.attnum AS dtd_identifier FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname 'pg_catalog'::name OR co.collname 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) ) t WHERE 1 = 1 AND UPPER ( T.TABLE_NAME ) 'DIS_USER_DATARIGHT_IF_SPLIT_T' AND UPPER ( T.TABLE_NAME ) NOT LIKE'DIS_TMP_%' AND UPPER ( T.COLUMN_NAME ) '_DISAPP_AUTO_ID_' AND ( ( T.TABLE_NAME ), ( T.table_schema ) ) IN ( ( lower ( 'table_name' )::name, lower ( 'schema_name' )::name ) );例如，快速查询表promotion的列信息： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 SELECT /*+ set (enable_hashjoin off) */T.table_schema AS tableschema, T.TABLE_NAME AS tablename, T.dtd_identifier AS srcAttrId, COLUMN_NAME AS fieldName, 'N' AS isPrimaryKey, nvl ( nvl ( T.character_maximum_length, T.numeric_precision ), 0 ) AS fieldLength, T.udt_name AS fieldType from ( SELECT /*+ indexscan(co) indexscan(nco) indexscan(a) indexscan(t) leading((nc c a)) leading((co nco)) indexscan(bt) indexscan(nt) */ nc.nspname AS table_schema, c.relname AS table_name, a.attname AS column_name, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, a.attnum AS dtd_identifier FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname 'pg_catalog'::name OR co.collname 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) ) t WHERE 1 = 1 AND UPPER ( T.TABLE_NAME ) 'DIS_USER_DATARIGHT_IF_SPLIT_T' AND UPPER ( T.TABLE_NAME ) NOT LIKE'DIS_TMP_%' AND UPPER ( T.COLUMN_NAME ) '_DISAPP_AUTO_ID_' AND ( ( T.TABLE_NAME ), ( T.table_schema ) ) IN ( ( lower ( 'promotion' )::name, lower ( 'public' )::name ) ); 通过查询审计日志获取表定义。使用函数pgxc_query_audit可以查询所有CN节点的审计日志，其语法为： 1 pgxc_query_audit(timestamptz startime,timestamptz endtime)查询审计多个对象名的记录： 1 2 SET audit_object_name_format TO 'all'; SELECT object_name,result,operation_type,command_text FROM pgxc_query_audit('2024-05-26 8:00:00','2024-05-26 22:55:00') where command_text like '%student%'; 查询表大小 查询表的总大小（包含表的索引和数据）。 1 SELECT pg_size_pretty(pg_total_relation_size('.'));示例： 先在customer_t1创建索引： 1 CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);然后查询public模式下，customer_t1表的大小。 1 2 3 4 5 SELECT pg_size_pretty(pg_total_relation_size('public.customer_t1')); pg_size_pretty ---------------- 264 kB (1 row) 查询表的数据大小（不包括索引）。 1 SELECT pg_size_pretty(pg_relation_size('.')); 示例：查询public模式下，customer_t1表的大小。 1 2 3 4 5 SELECT pg_size_pretty(pg_relation_size('public.customer_t1')); pg_size_pretty ---------------- 208 kB (1 row) 查询系统中所有表占用空间大小排行。 1 2 3 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit xx; 示例1：查询系统中所有表占用空间大小排行前15。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 15; table_full_name | size ---------------------------+--------- pg_catalog.pg_attribute | 2048 KB pg_catalog.pg_rewrite | 1888 KB pg_catalog.pg_depend | 1464 KB pg_catalog.pg_proc | 1464 KB pg_catalog.pg_class | 512 KB pg_catalog.pg_description | 504 KB pg_catalog.pg_collation | 360 KB pg_catalog.pg_statistic | 352 KB pg_catalog.pg_type | 344 KB pg_catalog.pg_operator | 224 KB pg_catalog.pg_amop | 208 KB public.tt1 | 160 KB pg_catalog.pg_amproc | 120 KB pg_catalog.pg_index | 120 KB pg_catalog.pg_constraint | 112 KB (15 rows) 示例2：查询public模式下所有表占用空间排行前20。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_schema='public' ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20; table_full_name | size -----------------------------+--------- public.tt1 | 160 KB public.product_info_input | 112 KB public.customer_t1 | 96 KB public.warehouse_t19 | 48 KB public.emp | 32 KB public.customer | 0 bytes public.test_trigger_src_tbl | 0 bytes public.warehouse_t1 | 0 bytes (8 rows) 快速查询全库中所有表占用空间大小8.1.3及以上集群版本在大集群大数据量（表数量大于1000）场景下，如果进行全库表查询，建议优先使用pgxc_wlm_table_distribution_skewness视图，该视图可以查到全库内的各表空间使用情况以及数据倾斜分布情况。其中，total_size和avg_size的单位为字节（bytes）。 1 2 3 4 5 6 7 SELECT *, pg_size_pretty(total_size) as tableSize FROM pgxc_wlm_table_distribution_skewness ORDER BY total_size desc; schema_name | table_name | total_size | avg_size | max_percent | min_percent | skew_percent | tablesize --------------------+---------------------------------------------------+------------+-----------+-------------+-------------+--------------+----------- public | history_tbs_test_row_1 | 804347904 | 134057984 | 18.02 | 15.63 | 7.53 | 767 MB public | history_tbs_test_row_3 | 402096128 | 67016021 | 18.30 | 15.60 | 8.90 | 383 MB public | history_tbs_test_row_2 | 401743872 | 66957312 | 18.01 | 15.01 | 7.47 | 383 MB public | i_history_tbs_test_1 | 325263360 | 54210560 | 17.90 | 15.50 | 6.90 | 310 MB查询结果显示history_tbs_test_row_1表占用空间最大，且数据有一定的倾斜。 
视图pgxc_wlm_table_distribution_skewness需要打开GUC参数use_workload_manager和enable_perm_space才能进行查询，在低版本查询全库时建议使用table_distribution()函数，如果仅查询某一张表的大小，推荐使用table_distribution(schemaname text, tablename text)函数。
8.2.1及以上集群版本中，GaussDB(DWS)已支持pgxc_wlm_table_distribution_skewness视图，可直接查询。
在8.1.3集群版本中，可使用如下定义创建视图后再进行查询：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE OR REPLACE VIEW
pgxc_wlm_table_distribution_skewness AS
WITH skew AS
(
SELECT
schemaname,
tablename,
pg_catalog.sum(dnsize)
AS totalsize,
pg_catalog.avg(dnsize)
AS avgsize,
pg_catalog.max(dnsize)
AS maxsize,
pg_catalog.min(dnsize)
AS minsize,
(maxsize
- avgsize) * 100 AS skewsize
FROM
pg_catalog.gs_table_distribution()
GROUP
BY schemaname, tablename
)
SELECT
schemaname AS schema_name,
tablename AS table_name,
totalsize AS total_size,
avgsize::numeric(1000) AS avg_size,
(
CASE
WHEN totalsize = 0 THEN 0.00
ELSE (maxsize * 100 /
totalsize)::numeric(5, 2)
END
) AS max_percent,
(
CASE
WHEN totalsize = 0 THEN 0.00
ELSE (minsize * 100 /
totalsize)::numeric(5, 2)
END
) AS min_percent,
(
CASE
WHEN totalsize = 0 THEN 0.00
ELSE (skewsize /
maxsize)::numeric(5, 2)
END
) AS skew_percent
FROM skew;
查询数据库
使用gsql的\l元命令查看数据库系统的数据库列表。
1
2
3
4
5
6
7
8
9
10
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
gaussdb | Ruby | SQL_ASCII | C | C |
template0 | Ruby | SQL_ASCII | C | C | =c/Ruby +
| | | | | Ruby=CTc/Ruby
template1 | Ruby | SQL_ASCII | C | C | =c/Ruby +
| | | | | Ruby=CTc/Ruby
(3 rows)
如果用户在数据库安装的时候没有指定LC_COLLATE、LC_CTYPE参数，则LC_COLLATE、LC_CTYPE参数的默认值为C。
如果用户在创建数据库时没有指定LC_COLLATE、LC_CTYPE参数，则默认使用模板数据库的排序顺序及字符分类。详细内容可参见CREATE DATABASE参数说明。 通过系统表pg_database查询数据库列表。 1 2 3 4 5 6 7 SELECT datname FROM pg_database; datname ----------- template1 template0 gaussdb (3 rows) 查询数据库大小 查询数据库的大小。 1 select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;示例： 1 2 3 4 5 6 7 select datname,pg_size_pretty(pg_database_size(datname)) from pg_database; datname | pg_size_pretty -----------+---------------- template1 | 61 MB template0 | 61 MB postgres | 320 MB (3 rows) 查询指定SCHEMA下的表大小及表对应索引的大小 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname='public' ORDER BY 1,2; |
【本文地址】
今日新闻 |
推荐新闻 |